package com.turing.post.organization.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.organization.util.IssueBranch;
import com.turing.post.organization.util.IssueBranchDao;
import com.turing.post.organization.util.SaleBranch;
import com.turing.post.organization.util.SaleBranchDao;
import com.turing.post.organization.util.SysBranch;
import com.turing.post.organization.util.SysBranchDao;
import com.turing.post.util.Config;
import com.turing.post.util.DropdownList;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * @author zxp
 * @Dec 18, 2012
 */
@Stateless(mappedName = "organization.impl.IssueBranchDaoImpl")
@Remote
@Local
public class IssueBranchDaoImpl implements IssueBranchDao {

	@EJB(mappedName = "organization.impl.SysBranchDaoImpl")
	private SysBranchDao sysBranchDaoImpl;

	@EJB(mappedName = "organization.impl.SaleBranchDaoImpl")
	private SaleBranchDao saleBranchDaoImpl;
	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@SuppressWarnings("unchecked")
	@Override
	public IssueBranch getIssueBranchByIssueBranchName(String issueBranchName) {
		String jpql = "select * from IssueBranch c where c.IssueBranchName ='"
				+ issueBranchName + "'";
		Query query = _em.createNativeQuery(jpql, IssueBranch.class);
		List<IssueBranch> list = query.getResultList();
		if (list == null || list.isEmpty() || list.size() < 1) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public IssueBranch getIssueBranchByIssueBranchNo(String issueBranchNo) {
		return _em.find(IssueBranch.class, issueBranchNo);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<IssueBranch> getIssueBranchList(PCPSWebInput webInput,
			String issueBranchNo, String issueBranchName, String regionNo,
			String status, String allowActivate, Integer page) {
		String sql = "select * from IssueBranch t where 1=1";
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String sqlCond = "";
		if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			sqlCond = " and t.IssueBranchNo in(select subBranchNo from sysbranchsubord t where t.branchno='"
					+ webInput.getOperBranchNo() + "' and SubBranchType='11')";
		}
		sql = sql + sqlCond;
		if (!PCPSUtil.isEmpty(issueBranchNo)) {
			sql = sql + " and t.IssueBranchNo like '%" + issueBranchNo + "%'";
		}
		if (!PCPSUtil.isEmpty(issueBranchName)) {
			sql = sql + " and t.IssueBranchName like '%" + issueBranchName
					+ "%'";
		}
		if (!PCPSUtil.isEmpty(regionNo)) {
			sql = sql + " and t.RegionNo like '" + regionNo + "%'";
		}
		if (!PCPSUtil.isEmpty(status)) {
			sql = sql + " and t.Status = '" + status + "'";
		}
		if (!PCPSUtil.isEmpty(allowActivate)) {
			sql = sql + " and t.allowActivate = '" + allowActivate + "'";
		}
		sql = sql + "order by IssueBranchNo";
		Query query = _em.createNativeQuery(sql, IssueBranch.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		return query.getResultList();
	}

	@Override
	public BigInteger getIssueBranchTotal(PCPSWebInput webInput,
			String issueBranchNo, String issueBranchName, String regionNo,
			String status, String allowActivate) {
		String sql = "select nvl(count(*),0) from IssueBranch t where 1=1";
		String sqlCond = "";
		if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			sqlCond = " and t.IssueBranchNo in(select subBranchNo from sysbranchsubord t where t.branchno='"
					+ webInput.getOperBranchNo() + "' and SubBranchType='11')";
		}
		sql = sql + sqlCond;
		if (!PCPSUtil.isEmpty(issueBranchNo)) {
			sql = sql + " and t.IssueBranchNo like '%" + issueBranchNo + "%'";
		}
		if (!PCPSUtil.isEmpty(issueBranchName)) {
			sql = sql + " and t.IssueBranchName like '%" + issueBranchName
					+ "%'";
		}
		if (!PCPSUtil.isEmpty(regionNo)) {
			sql = sql + " and t.RegionNo like '" + regionNo + "%'";
		}
		if (!PCPSUtil.isEmpty(status)) {
			sql = sql + " and t.Status = '" + status + "'";
		}
		if (!PCPSUtil.isEmpty(allowActivate)) {
			sql = sql + " and t.allowActivate = '" + allowActivate + "'";
		}
		Query query = _em.createNativeQuery(sql);
		return new BigInteger(query.getSingleResult().toString());
	}

	// @SuppressWarnings("unchecked")
	// @Override
	// public List<IssueBranch> getIssueBranchDropList(){
	// String sql = "select * from IssueBranch t where t.allowactivate='1' order
	// by IssueBranchNo";
	// sql = sql + "";
	// Query query = _em.createNativeQuery(sql, IssueBranch.class);
	// return query.getResultList();
	// }

	@Override
	public void insertIssueBranch(IssueBranch issueBranch)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, issueBranch);
	}

	@Override
	public void updateIssueBranch(IssueBranch issueBranch)
			throws PCPSApplicationException {
		GeneralDao.update(_em, issueBranch);
	}

	@Override
	public void deleteIssueBranch(String branchNo)
			throws PCPSApplicationException {
		try {
			String sql2 = "delete from IssueBranch where IssueBranchNo='"
					+ branchNo + "'";
			Query query2 = _em.createNativeQuery(sql2);
			query2.executeUpdate();
		} catch (Exception e) {
			throw new PCPSApplicationException(
					PCPSApplicationException.ERRCODE_CATEGORY_DATABASE,
					"delete","该机构已被引用，禁止删除!" , false);//e.getMessage()
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getIssueBranchReport(PCPSWebInput webInput) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select i.issuebranchno issuebranchno,i.issuebranchname issuebranchname,i.superissuebranchno superissuebranchno,si.issuebranchname superissuebranchname,i.regionno regionno,'发卡机构' branchtype,i.linkman linkman,i.linktele linktele,i.address address "
				+ "from issuebranch i left join issuebranch si on i.superissuebranchno = si.issuebranchno "
				+ "where (?1 is null or substr(i.issuebranchno ,0,6)=?1) order by i.issuebranchno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		List<Object[]> list = query.getResultList();
		return list;
	}

	// @SuppressWarnings("unchecked")
	// @Override
	// public List<OrganTreeList> getIssueBranchListByIssueBranchNo(
	// String branchNo, Integer minLen) {
	// String jpql = "select ib.issuebranchno branchNo,ib.issuebranchname
	// branchName,nvl(ib.superissuebranchno,'0') superBranchNo,"
	// + "case when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=0 then 1 when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=2 then 2 when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=4 then 3 when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=6 then 4 when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=8 then 5 "
	// + "when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=10 then 6 end as layer from IssueBranch ib where ib.issuebranchno
	// like '"
	// + branchNo + "%' order by ib.issuebranchno";
	// // 超级管理员全部显示
	// if (Config.SUPER_SYSOPUER_BRANCHNO.equals(branchNo)) {
	// jpql = "select ib.issuebranchno branchNo,ib.issuebranchname
	// branchName,nvl(ib.superissuebranchno,'0') superBranchNo,"
	// + "case when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=0 then 1 when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=2 then 2 when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=4 then 3 when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=6 then 4 when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=8 then 5 "
	// + "when (length(ib.issuebranchno)-"
	// + minLen
	// + ")=10 then 6 end as layer from IssueBranch ib order by
	// ib.issuebranchno";
	// }
	// Query query = _em.createNativeQuery(jpql, OrganTreeList.class);
	// List<OrganTreeList> list = query.getResultList();
	// return list;
	// }
	//
	// @Override
	// public Integer getIssueBranchMinIssueBranchNo(String branchNo) {
	// String sql = "select nvl(min(length(ib.issuebranchno)),0) minlength from
	// IssueBranch ib where ib.issuebranchno like '"
	// + branchNo + "%'";
	// // 超级管理员全部显示
	// if (Config.SUPER_SYSOPUER_BRANCHNO.equals(branchNo)) {
	// sql = "select nvl(min(length(ib.issuebranchno)),0) minlength from
	// IssueBranch ib";
	// }
	// Query query = _em.createNativeQuery(sql);
	// return ((BigDecimal) query.getSingleResult()).intValue();
	// }

	@SuppressWarnings("unchecked")
	@Override
	public List<DropdownList> getTransferBrachDropDownListBy(
			PCPSWebInput webInput) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		String operBranchNo = webInput.getOperBranchNo();
		// 判断登录用户归属机构
		SysBranch sys = sysBranchDaoImpl.getSysBranchByBranchNo(operBranchNo);
		// 发卡商机构
		if (sys.getBranchType().equals(SysBranch.SYSBRANCH_ISSUEBRANCH)) {
			List<DropdownList> resultList = new ArrayList<DropdownList>();
			// 调出机构map
			HashMap<String, String> map = new HashMap<String, String>();
			List<IssueBranch> ibList = getIssueBranchListBySuperIssueBranchNo(operBranchNo);
			// 获取发卡商机构上级直属机构
			IssueBranch superIb = getIssueBranchByIssueBranchNo(operBranchNo);
			// 上级机构号不为空时
			if (!PCPSUtil.isEmpty(superIb.getSuperIssueBranchNo())) {
				map.put(superIb.getSuperIssueBranchNo(),
						getIssueBranchByIssueBranchNo(
								superIb.getSuperIssueBranchNo())
								.getIssueBranchName());
			}
			// 如果没有直接下级发卡商机构列表,获取该机构下销售商列表下所有销售机构列表
			if (ibList != null && ibList.size() > 0) {
				// 获取直接下级发卡商机构列表
				for (IssueBranch ib : ibList) {
					map.put(ib.getIssueBranchNo(), ib.getIssueBranchName());
				}
			}
			// 发卡商机构可以直接给下级销售机构调拨
			List<SaleBranch> sbList = getSaleBranchListByIssueBranchNo(operBranchNo);
			if (sbList != null && sbList.size() > 0) {
				for (SaleBranch sb : sbList) {
					map.put(sb.getSaleBranchNo(), sb.getSaleBranchName());
				}

			}
			String key = "";
			for (Iterator<?> it = map.entrySet().iterator(); it.hasNext();) {
				Map.Entry e = (Map.Entry) it.next();
				key = e.getKey().toString();
				resultList.add(new DropdownList(key, map.get(key)));
			}

			return resultList;
		}
		// 销售商机构
		if (sys.getBranchType().equals(SysBranch.SYSBRANCH_SALEBRANCH)) {
			List<DropdownList> resultList = new ArrayList<DropdownList>();
			// 获取上级销售机构
			SaleBranch superSb = saleBranchDaoImpl
					.getSaleBranchBySaleBranchNo(operBranchNo);
			if (!PCPSUtil.isEmpty(superSb.getSuperSaleBranchNo())) {
				resultList.add(new DropdownList(superSb.getSuperSaleBranchNo(),
						saleBranchDaoImpl.getSaleBranchBySaleBranchNo(
								superSb.getSuperSaleBranchNo())
								.getSaleBranchName()));
			} else {
				// 上级销售机构为空，获取发卡机构
				IssueBranch superIb = getIssueBranchByIssueBranchNo(superSb
						.getIssueBranchNo());
				resultList.add(new DropdownList(superIb.getIssueBranchNo(),
						superIb.getIssueBranchName()));
			}
			// 获取下级销售机构
			List<SaleBranch> sbList = getSaleBranchListBySuperSaleBranchNo(operBranchNo);
			if (sbList != null && sbList.size() > 0) {
				for (SaleBranch sb : sbList) {
					resultList.add(new DropdownList(sb.getSaleBranchNo(), sb
							.getSaleBranchName()));
				}
			}
			return resultList;
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	private List<SaleBranch> getSaleBranchListByIssueBranchNo(String branchNo) {
		String sql = "select t.* from salebranch t where t.issueBranchNo ='"
				+ branchNo
				+ "' and SuperSaleBranchNo is null order by t.salebranchno";
		Query query = _em.createNativeQuery(sql, SaleBranch.class);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	private List<IssueBranch> getIssueBranchListBySuperIssueBranchNo(
			String branchNo) {
		String sql = "select t.* from issuebranch t where t.superissuebranchno = '"
				+ branchNo + "' order by t.issuebranchno";
		Query query = _em.createNativeQuery(sql, IssueBranch.class);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	private List<SaleBranch> getSaleBranchListBySuperSaleBranchNo(
			String branchNo) {
		String sql = "select t.* from salebranch t where t.supersalebranchno = '"
				+ branchNo + "' order by t.salebranchno";
		Query query = _em.createNativeQuery(sql, SaleBranch.class);
		return query.getResultList();
	}

	@Override
	public IssueBranch getIssueBranchByIssueBranchNoForUpdate(
			String issueBranchNo) {
		String sql = "select * from IssueBranch t where t.issuebranchno=?1 for update";
		Query query = _em.createNativeQuery(sql, IssueBranch.class);
		query.setParameter(1, issueBranchNo);
		return (IssueBranch) query.getSingleResult();
	}

	/**
	 * 查询某发卡商下的发卡分支机构列表
	 * 
	 * @param issueCompanyNo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<IssueBranch> getAllIssueBranchByIssueCompanyNo(
			String issueCompanyNo) {
		String sql = "select t.* from IssueBranch t where substr(t.issuebranchno,0,6) = ?1";
		Query query = _em.createNativeQuery(sql, IssueBranch.class);
		query.setParameter(1, issueCompanyNo);
		return query.getResultList();
	}

	/**
	 * 根据发卡机构号，时间段查询授信额度结余统计表
	 * 
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getCreditAccDetailByIssueBranch(
			PCPSWebInput webInput, String issueBranchNo, String startdate,
			String enddate) {
		if (webInput == null || PCPSUtil.isEmpty(startdate)
				|| PCPSUtil.isEmpty(enddate) || PCPSUtil.isEmpty(issueBranchNo)) {
			return null;
		}
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select a.issuebranchname,a.creditBalance,"
				+ " (select nvl(sum(Amount), 0) from CreditAccDetail a1  where TranType = '30' and a1.issuebranchno=a.issuebranchno and TranDate >=to_date(?1,'yyyy-mm-dd') and TranDate<=to_date(?2,'yyyy - mm - dd')) as Aamount1,"
				+ " (select nvl(sum(Amount), 0) from CreditAccDetail a2  where TranType = '20' and a2.issuebranchno=a.issuebranchno and TranDate >=to_date(?1,'yyyy-mm-dd') and TranDate<=to_date(?2,'yyyy - mm - dd')) as Aamount2,"
				+ " (select nvl(sum(Amount), 0) from CreditAccDetail a3  where TranType = '30' and a3.issuebranchno=a.issuebranchno and TranDate<=to_date(?2,'yyyy - mm - dd')) as Aamount3,"
				+ " (select nvl(sum(Amount), 0) from CreditAccDetail a4  where TranType = '20' and a4.issuebranchno=a.issuebranchno and TranDate<=to_date(?2,'yyyy - mm - dd')) as Aamount4"
				+ " from IssueBranch a where 1=1 and (a.IssueCompanyNo is null or a.IssueCompanyNo='"
				+ issuecompanyno + "')";
		if (!PCPSUtil.isEmpty(issueBranchNo)) {
			sql = sql
					+ " and  a.issuebranchno in (select distinct(t.subbranchno) from sysbranchsubord t where t.subbranchtype = '11' and  t.branchno = '"
					+ issueBranchNo + "')";
		}
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startdate);
		query.setParameter(2, enddate);
		List<Object[]> list = query.getResultList();
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getIssueBranchFeeForTranDate(String issueCompNo, String saleBranchs,
			Date trandate) throws PCPSApplicationException {
		String date = PCPSUtil.getStringYMDByDate(trandate);
		String sql = "SELECT SUM(X.CARDFEE), SUM(X.CASHFEE)"
			+"FROM ( "
			+"(SELECT  "
			+"NVL(SUM(DECODE(A.CardFee, 0, 0, A.CardFee-A.DiscountFee)), 0) CARDFEE,  "
			+"NVL(SUM(DECODE(A.CASHFEE, 0, 0, A.CASHFEE-A.DISCOUNTFEE)), 0) CASHFEE  "
			+"FROM CARDEVENTLOG A "
			+"WHERE A.TRANDATE = TO_DATE('"+date+"', 'YYYY-MM-DD') "
			+"AND A.BRANCHNO IN ("+saleBranchs+") " 
			+"AND A.EVENTTYPE IN ('03', '11', '13', '20','21','22', '23', '24', '30', '40','41','50', '51', '97', '98', '99') " 
			+"AND A.ISSUECOMPANYNO = '" + issueCompNo + "') "
			+"UNION ALL "
			+"(SELECT 0 CARDFEE, NVL(SUM(B.PAIDAMOUNT), 0) CASHFEE "
			+"FROM SALEORDERPAIDDETAIL B LEFT JOIN SALEORDER C ON B.SALEORDERID = C.SALEORDERID "
			+"WHERE B.PAIDCLASS = '1' "
			+"AND B.TRANDATE = TO_DATE('"+date+"', 'YYYY-MM-DD') "
			+"AND C.SALEBRANCHNO IN ("+saleBranchs+")) "
			+"UNION ALL "
			+"(SELECT 0 CARDFEE, NVL(SUM(D.PAIDAMOUNT), 0) CASHFEE "
			+"FROM DEPOSITORDERPAIDDETAIL D LEFT JOIN DEPOSITORDER E ON D.DEPOSITORDERID = E.DEPOSITORDERID  "
			+"WHERE D.PAIDCLASS = '1' "
			+"AND D.TRANDATE = TO_DATE('"+date+"', 'YYYY-MM-DD') "
			+"AND E.SALEBRANCHNO IN ("+saleBranchs+")) "
			+"UNION ALL "
			+"(SELECT NVL(SUM(F.TOTALFEE), 0) CARDFEE, 0 CASHFEE "
			+"FROM REDEMPORDER F "
			+"WHERE F.LASTREDEMPDATE = TO_DATE('"+date+"', 'YYYY-MM-DD') "
			+"AND (F.ORDERSTATUS = '4' OR  F.ORDERSTATUS = '5') "
			+"AND F.SALEBRANCHNO IN ("+saleBranchs+"))) X";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<IssueBranch> getIssueBranchAllowActivateByIssueCompanyNo(
			String issueCompanyNo) {
		String sql = "select t.* from IssueBranch t where t.AllowActivate = '1' and  substr(t.issuebranchno,0,6) = ?1";
		Query query = _em.createNativeQuery(sql, IssueBranch.class);
		query.setParameter(1, issueCompanyNo);
		return query.getResultList();
	}
}